package org.example.mock;

import lombok.extern.slf4j.Slf4j;
import org.example.bean.MovieCountryCount;

import java.sql.*;
import java.util.*;

/**
 * 用于生成 MovieCountryCount 模拟数据的生成器类，并将数据插入数据库
 */
@Slf4j
public class MovieCountryCountGenerator {
    // 数据库连接信息，根据实际情况修改
    private static final String DB_URL = "jdbc:mysql://192.168.10.102/changchengfilm?requireSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "000000";

    private static final Random RANDOM = new Random();
    // 最大尝试次数，防止无限循环
    private static final int MAX_ATTEMPTS = 1000;

    public static void main(String[] args) {
        log.info("开始生成 MovieCountryCount 模拟数据并插入数据库");
        try (Connection conn = getConnection()) {
            // 清空表数据（可选，根据需要决定是否保留）
            clearTable(conn);

            // 生成并插入100条模拟数据
            int count = 100;
            List<MovieCountryCount> dataList = generateMovieCountryCountData(conn, count);
            insertData(conn, dataList);

            log.info("成功插入 {} 条 MovieCountryCount 数据到数据库", dataList.size());

        } catch (SQLException e) {
            log.error("数据库操作失败", e);
        }
    }

    /**
     * 获取数据库连接
     */
    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
    }

    /**
     * 清空表数据
     */
    private static void clearTable(Connection conn) throws SQLException {
        String sql = "DELETE FROM movie_country_count";
        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
            log.info("已清空 movie_country_count 表数据");
        }
    }

    /**
     * 生成指定数量的 MovieCountryCount 模拟数据
     */
    public static List<MovieCountryCount> generateMovieCountryCountData(Connection conn, int count) throws SQLException {
        // 从movies_data表获取所有有效的movie_id
        List<Integer> validMovieIds = getValidMovieIds(conn);
        // 从countries表获取所有有效的country_id
        List<Integer> validCountryIds = getValidCountryIds(conn);

        if (validMovieIds.isEmpty() || validCountryIds.isEmpty()) {
            log.error("没有找到有效的movie_id或country_id，请确保主表中有数据");
            return Collections.emptyList();
        }

        List<MovieCountryCount> resultList = new ArrayList<>();
        // 使用Set来保证(movie_id, country_id)组合的唯一性
        Set<String> uniqueKeys = new HashSet<>();

        int attempts = 0;
        while (resultList.size() < count && attempts < MAX_ATTEMPTS) {
            attempts++;
            // 从有效ID列表中随机选择
            int movieId = validMovieIds.get(RANDOM.nextInt(validMovieIds.size()));
            int countryId = validCountryIds.get(RANDOM.nextInt(validCountryIds.size()));
            String key = movieId + "_" + countryId;

            // 检查唯一性
            if (!uniqueKeys.contains(key)) {
                uniqueKeys.add(key);
                int isPrimary = RANDOM.nextInt(2);
                MovieCountryCount data = new MovieCountryCount(movieId, countryId, isPrimary);
                resultList.add(data);
            }
        }

        if (attempts >= MAX_ATTEMPTS) {
            log.warn("达到最大尝试次数，可能无法生成指定数量的唯一数据");
        }

        return resultList;
    }

    /**
     * 从数据库获取有效的movie_id列表
     */
    private static List<Integer> getValidMovieIds(Connection conn) throws SQLException {
        List<Integer> movieIds = new ArrayList<>();
        String sql = "SELECT movie_id FROM movies_data";
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                movieIds.add(rs.getInt("movie_id"));
            }
        }
        log.info("获取到 {} 个有效 movie_id", movieIds.size());
        return movieIds;
    }

    /**
     * 从数据库获取有效的country_id列表
     */
    private static List<Integer> getValidCountryIds(Connection conn) throws SQLException {
        List<Integer> countryIds = new ArrayList<>();
        String sql = "SELECT country_id FROM countries";
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                countryIds.add(rs.getInt("country_id"));
            }
        }
        log.info("获取到 {} 个有效 country_id", countryIds.size());
        return countryIds;
    }

    /**
     * 将数据插入数据库
     */
    private static void insertData(Connection conn, List<MovieCountryCount> dataList) throws SQLException {
        String sql = "INSERT INTO movie_country_count (movie_id, country_id, is_primary) VALUES (?, ?, ?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            for (MovieCountryCount data : dataList) {
                pstmt.setInt(1, data.getMovieId());
                pstmt.setInt(2, data.getCountryId());
                pstmt.setInt(3, data.getIsPrimary());
                pstmt.executeUpdate();
            }
        }
    }
}